package com.ids.transfer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.ids.transfer.config.Constant;
import com.ids.transfer.util.DbUtils;
import com.ids.transfer.util.SQLParseUtil;
import com.ids.transfer.util.StrCharUtil;

/**
 * the function which used for Extra-data.
 */
public class ExtraExecute {
	
	static Class<?> ExtraExecute;
	protected static Log logger = LogFactory.getLog(ExtraExecute.class);
	
	/**
	 * @param srcSQL 
	 * 			select SQL
	 * @param srcConn
	 * @param desConn  
	 * @param desSQL insert sql
	 * @param batchNum 最大插入数
	 * @throws java.lang.Exception
	 */
	public static void batchRs(String srcSQL,
			Connection srcConn, Connection desConn, String desSQL, int batchNum)
			throws Exception {
		Statement st = srcConn.createStatement();
		ResultSet rs = st.executeQuery(srcSQL);
		batchRs(rs, desConn, desSQL, batchNum);
	}

	/**
	 * 
	 * @param srcSQL
	 * @param srcConn
	 * @param desConn
	 * @param desSQL
	 * @param pk 			as: id,name
	 * @param batchNum
	 * @throws Exception
	 */
	public static void batchRs(String srcSQL,String fromPk,
			Connection srcConn, Connection desConn, String desSQL,String toPk, int batchNum)
			throws Exception {
		
		Statement st = srcConn.createStatement();
		ResultSet rs = st.executeQuery(srcSQL);
		batchRs(rs, desConn, desSQL,fromPk,toPk, batchNum);
	}

	/** 
	 * 有则更新，无则插入
	 * @param rs
	 * @param desConn
	 * @param desSQL	insert语句
	 * @param pk
	 * @param batchNum
	 * @throws java.lang.Exception
	 */
	public static void batchRs(ResultSet rs, Connection desConn, String desSQL,
			String fromPk,String toPk, int batchNum) throws Exception {
		
		if(toPk==null||toPk.equals("")){
			return;
		}
		// select id,name from mytest3 where id=?
		// update mytest3 set name=? where id=?
		//从select语句转为update语句
		String uptSQL = SQLParseUtil.parserSQL(desSQL, toPk, "update");
		String tableName=SQLParseUtil.parserTableName(desSQL);
		// String pksrcSelStr = SQLParseUtil.parserSQL(desSQL, pk);
		//主键处理
		String[] fromPks=StrCharUtil.split(fromPk, ",");
		String[] toPks=StrCharUtil.split(toPk, ",");
		
		ResultSetMetaData rsmd = rs.getMetaData();
		int numCols = rsmd.getColumnCount();
		//批量
		int ibatch=0;
		//取一条，比一条，没有加入insert缓存，有则加入update缓存
		//2.每一句进行判断,多条完成
		PreparedStatement pstmtInsert = null;
		PreparedStatement pstmtUpate = null;
		while(rs.next()){
			//先考虑一个主键的情况,多主键未完成
			//表名完成
			//考虑不同数据结构
			//name =主键则getColumnType = int 则getInt 
			//=String 则getString
			++ibatch;
			//where id=?
			StringBuffer whereSql = getWhere(rs, fromPks, toPks, rsmd, numCols);
			//得到目标表是否含有该数据
			long rsCount = DbUtils.getRecordCount(desConn, tableName,
					whereSql.toString());
			//不同数据结构
			//System.out.println(rsCount);
			//如果记录大于1更新
			if (rsCount >= 1) {// 存在这条记录，需要更新,加入更新缓存
				pstmtUpate = desConn.prepareStatement(uptSQL);
				//System.out.println("更新");
				//更新的值从select 里得出
				//更新时需指出select的主
				// 参数化update语句
				setMetaData(rs, rsmd, numCols, pstmtUpate);
				//还要处理多出来的值,现在只能处理主键为number类型的
				for(int i=0;i<toPks.length;i++){
					//这里只用Object看是否可行
					pstmtUpate.setObject(numCols+i+1,rs.getObject(fromPks[i]));
				}//补充后面的where语句的值
				
				pstmtUpate.addBatch();
				if (ibatch >= batchNum) {
					pstmtUpate.executeBatch();
					//清理
					logger.info("batched"+Constant.BATCHNUM + " records.");
					System.runFinalization();
					System.gc();
					ibatch = 0;
					
				}
				//执行
				
			} else {//插入
				//当没数据时他也是一句一句插入，比较慢
				//System.out.println("插入");
				pstmtInsert = desConn.prepareStatement(desSQL);
				setMetaData(rs, rsmd, numCols, pstmtInsert);
				pstmtInsert.addBatch();
				if (ibatch >= batchNum) {
					pstmtInsert.executeBatch();
					//清理
					System.runFinalization();
					System.gc();
					ibatch = 0;
					logger.info("batched"+Constant.BATCHNUM + " records.");
				}
			}
		}//end while
		//执行
		pstmtInsert.executeBatch();
		//执行update
		pstmtUpate.executeBatch();
		
		if (pstmtInsert != null)
			pstmtInsert.close();
		if (pstmtUpate != null)
			pstmtUpate.close();
		logger.info("batched all records.");
		System.runFinalization();
		System.gc();
		desConn.commit();
	}

	private static void setMetaData(ResultSet rs, ResultSetMetaData rsmd,
			int numCols, PreparedStatement pstmt) throws SQLException {
		for (int i = 1; i <= numCols; i++) {
			//列类型转换
			switch (rsmd.getColumnType(i)) {
			case Types.BIT:
			case Types.DECIMAL:
			case Types.DOUBLE:
			case Types.FLOAT:
			case Types.INTEGER:
			case Types.NUMERIC:
			case Types.REAL:
			case Types.SMALLINT:
			case Types.TINYINT:
				pstmt.setInt(i, rs.getInt(i));
				break;
			case Types.DATE:
			case Types.TIME:
			case Types.TIMESTAMP:
				pstmt.setDate(i, rs.getDate(i));
				break;
			default:
				pstmt.setString(i, rs.getString(i));
			}
		}
	}

	private static StringBuffer getWhere(ResultSet rs, String[] fromPks,
			String[] toPks, ResultSetMetaData rsmd, int numCols)
			throws SQLException {
		//组装where语句,from 和 to 的主键数量要对应
		StringBuffer whereSql=new StringBuffer(255);
		for (int i=0;i<toPks.length;i++) {
			
			for (int col = 1; col <= numCols; col++) {
				if(fromPks[i].toUpperCase().equals(rsmd.getColumnName(col)))
				//列类型转换
				switch (rsmd.getColumnType(col)) {
				case Types.BIT:
				case Types.DECIMAL:
				case Types.DOUBLE:
				case Types.FLOAT:
				case Types.INTEGER:
				case Types.NUMERIC:
				case Types.REAL:
				case Types.SMALLINT:
				case Types.TINYINT:
					whereSql.append(toPks[i]+"="+rs.getInt(fromPks[i]));
					break;
				case Types.DATE:
				case Types.TIME:
				case Types.TIMESTAMP:
					whereSql.append(toPks[i]+"="+rs.getDate(fromPks[i]));
					break;
				default:
					whereSql.append(toPks[i]+"='"+rs.getString(fromPks[i])+"'");
				}
			}
			
			if(i!=toPks.length-1){
				whereSql.append(" and ");
			}
		}
		return whereSql;
	}
		

	/**
	 * 跟据数据集，批量插入数据 
	 * 
	 * @param taskid
	 *            任务号
	 * @param ResultSet
	 *            查询数据集
	 * @param desConn
	 *            目的连接
	 * @param desSQL
	 *            INSERT语句数组
	 * @param batchNum
	 *            最大一次批操作数目
	 * @throws java.lang.Exception
	 */
	public static void batchRs(ResultSet rs, Connection desConn,
			String desSQL, int batchNum) throws Exception {
		PreparedStatement pstmt = desConn.prepareStatement(desSQL);
		//取到列的元数据，包括类型长度
		ResultSetMetaData rsmd = rs.getMetaData();
		int numCols = rsmd.getColumnCount();
		int ibatch = 0;
		desConn.setAutoCommit(false);
		while (rs.next()) {
			++ibatch;
			setMetaData(rs, rsmd, numCols, pstmt);
			pstmt.addBatch();
			if (ibatch >= batchNum) {
				pstmt.executeBatch();
				//清理
				System.runFinalization();
				System.gc();
				ibatch = 0;
				logger.info("batched"+Constant.BATCHNUM + " records.");
			}
		}
		logger.info("batched all records.");
		System.runFinalization();
		System.gc();
		pstmt.executeBatch();
		if (pstmt != null)
			pstmt.close();
		desConn.commit();
	}

	
	public static void main(String[] args) {
		
	}
}